#!/usr/bin/python
# -*- coding: UTF-8 -*-
__author__ = 'gtlions'
import psycopg2
import csv
import os
import cx_Oracle
import datetime

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

dbgp = psycopg2.connect(dbname="gtlions", user="gpadmin", password="000000", host="192.168.100.60")
dbora = cx_Oracle.connect("gtlions", "000000", "192.168.100.60:1521/gt11g")

f = open("gp.csv", "w")
writer = csv.writer(f, lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)

print datetime.datetime.now(), "Begin query from source database."
cursorgp = dbgp.cursor()
cursorgp.execute("select * from test")
print datetime.datetime.now(), "Finsh query from source database."

i = 0
print datetime.datetime.now(), "Begin wirte record into local file."

for row in cursorgp:
    # i = i+1
    writer.writerow(row)
f.close()
print datetime.datetime.now(), "Finsh wirte record into local file"
dbgp.commit()
dbgp.close()

reader = csv.reader(open("gp.csv", "r"))

cursorora = dbora.cursor()
cursorora.execute("alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS.ff'")
cursorora.execute("truncate table test")
dbora.commit()
lines = []
commit = 3
i = 0
print datetime.datetime.now(), "Begin insert record into target database."
for line in reader:

    lines.append(line)
    if len(lines) == commit:
        i = i + 1
        current_commit = commit * i
        cursorora.executemany("insert into test values(:1,:2)", lines)
        dbora.commit()
        print datetime.datetime.now(), "------ insert " + str(current_commit).rjust(8) + " record into target database."
        # del(lines)
        lines = []
cursorora.executemany("insert into test values(:1,:2)", lines)
dbora.commit()
dbora.close()
print datetime.datetime.now(), "------ insert " + str(current_commit + len(lines)).rjust(
    8) + " record into target database."
print datetime.datetime.now(), "Finsh insert record into target database."
